﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class ComboBLL

    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable ListTrainingType()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select training_cat_seq as value, training_cat_descr as text"
                           + " from hrm_training_type_categories"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListTrainingCurriculums()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select training_type_seq as value, training_type_descr as text"
                           + " from hrm_training_types"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable ListTrainingCurriculumsPrerequisites()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select training_cur_seq as value, training_cur_descr as text"
                           + " from hrm_training_curriculums"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListTrainingCurriculumsSubject()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select training_subject_seq as value, training_subject_descr as text"
                           + " from hrm_training_subjects"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListTrainingCurriculumsCompare()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select compare_group_seq as value, compare_group_descr as text"
                           + " from hrm_training_compare_groups"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //public DataTable ListCountries()
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(1);
        //        dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
        //        string sql = " select country_seq as value, country_name as text"
        //                   + " from mas_countries"
        //                   + " where record_status=@RecordStatus";
        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        //        dbManager.Dispose();
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        //public DataTable ListDistrictTree(string parent_id)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@parent_id", parent_id);
        //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

        //        string sql = "SELECT main.* FROM( "
        //               + " SELECT CAST('0' AS CHAR) AS ID "
        //                   + ", CAST('ข้อมูลตำบล/อำเภอ/จังหวัด/ภาค' AS CHAR) AS NAME"
        //                   + ", NULL AS PARENT_ID"
        //                   + ", (select count(region_seq) from mas_regions"
        //                       + " where record_status not like @DeleteRecord"
        //                    + ") AS CHILDREN_COUNT"
        //                   + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
        //                   + ", NULL AS DESCRIPTION"
        //                   + ", 'TYPE' AS Category"
        //               + " UNION ALL "// ภาค
        //               + " SELECT CAST(CONCAT('0', '-', r.REGION_SEQ) AS CHAR) AS ID "
        //                   + ", r.REGION_NAME AS NAME"
        //                   + ", CAST('0' AS CHAR) AS PARENT_ID"
        //                   + ", (select count(prov_seq) from mas_provinces"
        //                       + " where record_status not like @DeleteRecord"
        //                       + " and region_seq = r.region_seq"
        //                   + ") AS CHILDREN_COUNT"
        //                   + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
        //                   + ", NULL AS DESCRIPTION"
        //                   + ", 'TYPE' AS Category"
        //               + " FROM MAS_REGIONS r"
        //                   + " WHERE r.RECORD_STATUS not like @DeleteRecord"

        //               + " UNION ALL "// จังหวัด
        //               + " SELECT CAST(CONCAT(p.REGION_SEQ, '-', p.PROV_SEQ) AS CHAR) AS ID "
        //                   + ", p.PROV_NAME AS NAME"
        //                   + ", CAST(CONCAT('0', '-', p.REGION_SEQ) AS CHAR) AS PARENT_ID"
        //                   + ", (select count(amp_seq) from mas_amphurs"
        //                       + " where record_status not like @DeleteRecord"
        //                       + " and prov_seq = p.prov_seq"
        //                   + ") AS CHILDREN_COUNT"
        //                   + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
        //                   + ", NULL AS DESCRIPTION"
        //                   + ", 'TYPE' AS Category"
        //               + " FROM MAS_PROVINCES p"
        //                   + " WHERE p.RECORD_STATUS not like @DeleteRecord"

        //               + " UNION ALL "// อำเภอ
        //               + " SELECT CAST(CONCAT(a.PROV_SEQ, '-', a.AMP_SEQ) AS CHAR) AS ID"
        //                   + ", a.AMP_NAME AS NAME"
        //                   + ", CAST(CONCAT(p.REGION_SEQ, '-', a.PROV_SEQ) AS CHAR) AS PARENT_ID"
        //                   + ", (select count(district_seq) from mas_districts"
        //                       + " where record_status not like @DeleteRecord"
        //                       + " and amp_seq = a.amp_seq"
        //                   + ") AS CHILDREN_COUNT"
        //                   + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
        //                   + ", NULL AS DESCRIPTION"
        //                   + ", 'TYPE' AS Category"
        //               + " FROM MAS_AMPHURS a"
        //                   + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
        //                   + " WHERE a.RECORD_STATUS not like @DeleteRecord"
        //                       + " AND p.RECORD_STATUS not like @DeleteRecord"
        //               + " UNION ALL "//ตำบล
        //               + " SELECT CAST(CONCAT(d.AMP_SEQ, '-', d.DISTRICT_SEQ) AS CHAR) AS ID"
        //                   + ", d.DISTRICT_NAME AS NAME"
        //                   + ", CAST(CONCAT(a.PROV_SEQ, '-', d.AMP_SEQ) AS CHAR) AS PARENT_ID"
        //                   + ", 0 AS CHILDREN_COUNT"
        //                   + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
        //                   + ", NULL AS DESCRIPTION"
        //                   + ", 'ITEM' AS Category"
        //               + " FROM MAS_DISTRICTS d"
        //                   + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
        //                   + " WHERE d.RECORD_STATUS not like @DeleteRecord"
        //                       + " AND a.RECORD_STATUS not like @DeleteRecord"
        //               + ") main"
        //               + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
        //               + " ORDER BY NAME";
        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        //        dbManager.Dispose();
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}
        public DataTable ListCountries()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select country_seq as value, country_name as text"
                           + " from mas_countries"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListDistrictTree(string parent_id)
        {
            try
            {
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@parent_id", parent_id);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string sql = "SELECT main.* FROM( "
                       + " SELECT CAST('0' AS CHAR) AS ID "
                           + ", CAST('ข้อมูลตำบล/อำเภอ/จังหวัด/ภาค' AS CHAR) AS NAME"
                           + ", NULL AS PARENT_ID"
                           + ", (select count(region_seq) from mas_regions"
                               + " where record_status not like @DeleteRecord"
                            + ") AS CHILDREN_COUNT"
                           + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                           + ", NULL AS DESCRIPTION"
                           + ", 'TYPE' AS Category"
                       + " UNION ALL "// ภาค
                       + " SELECT CAST(CONCAT('0', '-', r.REGION_SEQ) AS CHAR) AS ID "
                           + ", r.REGION_NAME AS NAME"
                           + ", CAST('0' AS CHAR) AS PARENT_ID"
                           + ", (select count(prov_seq) from mas_provinces"
                               + " where record_status not like @DeleteRecord"
                               + " and region_seq = r.region_seq"
                           + ") AS CHILDREN_COUNT"
                           + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                           + ", NULL AS DESCRIPTION"
                           + ", 'TYPE' AS Category"
                       + " FROM MAS_REGIONS r"
                           + " WHERE r.RECORD_STATUS not like @DeleteRecord"

                       + " UNION ALL "// จังหวัด
                       + " SELECT CAST(CONCAT(p.REGION_SEQ, '-', p.PROV_SEQ) AS CHAR) AS ID "
                           + ", p.PROV_NAME AS NAME"
                           + ", CAST(CONCAT('0', '-', p.REGION_SEQ) AS CHAR) AS PARENT_ID"
                           + ", (select count(amp_seq) from mas_amphurs"
                               + " where record_status not like @DeleteRecord"
                               + " and prov_seq = p.prov_seq"
                           + ") AS CHILDREN_COUNT"
                           + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                           + ", NULL AS DESCRIPTION"
                           + ", 'TYPE' AS Category"
                       + " FROM MAS_PROVINCES p"
                           + " WHERE p.RECORD_STATUS not like @DeleteRecord"

                       + " UNION ALL "// อำเภอ
                       + " SELECT CAST(CONCAT(a.PROV_SEQ, '-', a.AMP_SEQ) AS CHAR) AS ID"
                           + ", a.AMP_NAME AS NAME"
                           + ", CAST(CONCAT(p.REGION_SEQ, '-', a.PROV_SEQ) AS CHAR) AS PARENT_ID"
                           + ", (select count(district_seq) from mas_districts"
                               + " where record_status not like @DeleteRecord"
                               + " and amp_seq = a.amp_seq"
                           + ") AS CHILDREN_COUNT"
                           + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                           + ", NULL AS DESCRIPTION"
                           + ", 'TYPE' AS Category"
                       + " FROM MAS_AMPHURS a"
                           + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
                           + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                               + " AND p.RECORD_STATUS not like @DeleteRecord"
                       + " UNION ALL "//ตำบล
                       + " SELECT CAST(CONCAT(d.AMP_SEQ, '-', d.DISTRICT_SEQ) AS CHAR) AS ID"
                           + ", d.DISTRICT_NAME AS NAME"
                           + ", CAST(CONCAT(a.PROV_SEQ, '-', d.AMP_SEQ) AS CHAR) AS PARENT_ID"
                           + ", 0 AS CHILDREN_COUNT"
                           + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                           + ", NULL AS DESCRIPTION"
                           + ", 'ITEM' AS Category"
                       + " FROM MAS_DISTRICTS d"
                           + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
                           + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                               + " AND a.RECORD_STATUS not like @DeleteRecord"
                       + ") main"
                       //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                       + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id")
                       + " ORDER BY NAME";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable ListTrainingCourse()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select training_cur_seq as value, training_cur_descr as text"
                           + " from hrm_training_curriculums"
                           + " where record_status=@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
